package com.menggudf.common.utils;

import java.beans.PropertyDescriptor;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.springframework.util.StringUtils;

import com.menggudf.common.CellHeaderName;
import com.menggudf.model.SheetModel;

public class ExcelUtils {
	
	/**
	 * 支持导出多个工作簿内容
	 * 
	 * @param out				输出流
	 * @param modelClass		实际存储数据的类对象
	 * @param sheetModelList	工作簿信息
	 * @throws Exception
	 */
	public static void exportExcelEx(OutputStream out, List<SheetModel> sheetModelList) throws Exception{
		if(out == null || sheetModelList == null || sheetModelList.size() == 0){
			out.close();
			throw new RuntimeException("invalid param");
		}
		// 设定存入缓存中的固定行数的数据超过这些行数，就把旧的数据存入硬盘
		SXSSFWorkbook workbook = new SXSSFWorkbook(200);
		try {
			for (SheetModel sheetModel : sheetModelList) {
				if(sheetModel.getModelClass() == null){
					throw new RuntimeException("invalid param");
				}
				// 创建sheet
				Sheet sheet = creatSheet(workbook, sheetModel.getSheetName());
				fillData(workbook, sheet, sheetModel.getModelClass(), sheetModel.getList());
			}
			workbook.write(out);
		} catch (Exception e) {
			throw new Exception();
		} finally {
			out.close();
			workbook.dispose();
		}
		
	}

	/**
	 * 导出Excel
	 * 
	 * 说明：这个服务是一个较简单的excel导出，数据集里面的model必须符合javaBean规则，
	 * 并且model的属性类型为八大基本类型、date、String、集合（集合的泛型也是八大基本类型和字符串类型）其中之一。
	 * 
	 * @param out			输出流
	 * @param sheetName		Excel工作簿
	 * @param list			数据集
	 * @param modelClass	数据集存储对象的类对象
	 * @throws Exception	
	 */
	public static void exportExcel(OutputStream out, String sheetName, List<?> list, Class<?> modelClass) throws Exception {
		if(out == null || modelClass == null){
			out.close();
			throw new RuntimeException("invalid param");
		}
		//设定存入缓存中的固定行数的数据超过这些行数，就把旧的数据存入硬盘
		SXSSFWorkbook workbook = new SXSSFWorkbook(200);
		//创建sheet
		Sheet sheet = creatSheet(workbook, sheetName);
		
		try{
			fillData(workbook, sheet, modelClass, list);
			workbook.write(out);
		} catch (Exception e) {
			throw new Exception();
		} finally {
			out.close();
			workbook.dispose();
		}
	}
	
	private static Sheet creatSheet(Workbook workbook, String sheetName){
		Sheet sheet = null;
		if(StringUtils.isEmpty(sheetName)){
			sheet = workbook.createSheet();
		} else {
			sheet = workbook.createSheet(sheetName);
		}
		return sheet;
	}
	
	private static void fillData(Workbook workbook, Sheet sheet, Class<?> modelClass, List<?> list) throws Exception {

	    sheet.setDefaultColumnWidth((short) 20);
		// 创建[列首]字体
		Font headersFont = workbook.createFont();
		// 设置[列首]字体
		headersFont.setFontHeightInPoints((short) 13);
		// 设置粗体
		headersFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		// 设置字体
		headersFont.setFontName("宋体");

		// 创建[列首]样式
		CellStyle headersStyle = workbook.createCellStyle();
		// 居中
		headersStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 把[列首字体]应用到[列首样式]
		headersStyle.setFont(headersFont);
		// 行数编号
		int rowIndex = 0;
		// 创建第一行
		Row row = sheet.createRow(rowIndex);
		// 设置标题
		Field[] declaredFields = modelClass.getDeclaredFields();
		List<Boolean> rowFlagList = new ArrayList<Boolean>();
		boolean flag = true;
		int k = 0;
		for (int i = 0; i < declaredFields.length; i++) {
			CellHeaderName annotation = declaredFields[i].getAnnotation(CellHeaderName.class);
			String value = null;
			
			if (annotation != null) {
				value = annotation.value();
			}else{
			    flag =false;
			    rowFlagList.add(flag);
			    continue;
			}
			if (value == null || value == "") {
				value = declaredFields[i].getName();
			}
		    Cell cellHeader = row.createCell(k++);
            cellHeader.setCellStyle(headersStyle);
            cellHeader.setCellValue(value);

            flag =true;
            rowFlagList.add(flag);
			
		}

		// 创建长整形数字样式
		CellStyle longStyle = workbook.createCellStyle();
		DataFormat dataFormat = workbook.createDataFormat();
		longStyle.setDataFormat(dataFormat.getFormat("0"));

		// 填充数据
		if (list != null && list.size() != 0) {
			for (int i = 0; i < list.size(); i++) {
				rowIndex++;
				row = sheet.createRow(rowIndex);
				Field[] f = list.get(i).getClass().getDeclaredFields();
				int l = 0;
				for (int j = 0; j < f.length; j++) {
				    if(!rowFlagList.get(j)){
				        continue;
				    }
					PropertyDescriptor targetTypeProperty = new PropertyDescriptor(f[j].getName(),
							list.get(0).getClass());
					Method targetTypeMethod = targetTypeProperty.getReadMethod();
					Object value = targetTypeMethod.invoke(list.get(i));
					String textValue = null;
					Cell cell = row.createCell(l++);
					if (value != null) {
						if (value instanceof Date) {
							Date date = (Date) value;
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
							textValue = sdf.format(date);
						} else {
							// 其它数据类型都当作字符串简单处理
							textValue = value.toString();
						}
					}

					// 利用正则表达式判断textValue是否全部由数字组成
					if (textValue != null) {
						Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches() && textValue.length() <= 11) {
							// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							// 不是数字做普通处理
							cell.setCellValue(textValue);
						}
					}
				}
				// 超过200条，把旧数据往硬盘写
				if (i % 200 == 0) {
					((SXSSFSheet) sheet).flushRows(200);
				}
			}
		}
	}
}
